'use strict';

class Sqls {
  qylDataFunnel(data) {
    return `
          SELECT
	a.date 日期 ,
	'钱有路' as 活动归属,
	a. NO 注册人数 ,
	d. NO 完善资料人数 ,
	# d. NO / a. NO 完善资料率 ,
	CONCAT(LEFT(d. NO /a. NO * 100, 4), '%') 完善资料率,
	e. NO 通过风控数, 
  # e. NO / a. NO 风控通过率 ,
  CONCAT(LEFT(e. NO /a. NO * 100, 4), '%') 风控通过率,
	b. NO 建立账户数 ,
	# b. NO / a. NO 建立账户率 ,
	CONCAT(LEFT(b. NO /a. NO * 100, 4), '%') 建立账户率,
	f.p 提现人数 ,
	# f.p / a. NO 提现率 ,
	CONCAT(LEFT(f.p /a. NO * 100, 4), '%') 提现率 ,
	f. NO 提现笔数 ,
	f.amount 提现金额
FROM
	(
		SELECT
			substring(turl.create_time , 1 , 10) date ,
			count(turl.user_id) NO
		FROM
			t_user_registry_log_qyl turl
		WHERE
			turl.prod_code = 'QYL'
		AND turl.channel = '${data._id}'
		GROUP BY
			substring(turl.create_time , 1 , 10)
		ORDER BY
			substring(turl.create_time , 1 , 10) DESC
	) a
LEFT JOIN(
	SELECT
		substring(turl.create_time , 1 , 10) date ,
		count(DISTINCT(turl.user_id)) NO
	FROM
		t_user_registry_log_qyl turl
	LEFT JOIN base.t_account_application taa ON turl.user_id = taa.user_id
	LEFT JOIN t_account_application_patch taap ON taa.id = taap.apply_id
	WHERE
		taa.prod_code = 'QYL'
	AND turl.channel = '${data._id}'
	AND(
		taa. STATUS = 'Success'
		OR taap.patch_status = 'success'
	)
	GROUP BY
		substring(turl.create_time , 1 , 10)
	ORDER BY
		substring(turl.create_time , 1 , 10) DESC
) b ON a.date = b.date
LEFT JOIN(
	SELECT
		substring(turl.create_time , 1 , 10) date ,
		count(DISTINCT(turl.user_id)) NO
	FROM
		base.t_user_registry_log_qyl turl
	INNER JOIN base.t_account_application taa ON taa.user_id = turl.user_id
	WHERE
		taa.prod_code = 'QYL'
	AND turl.channel = '${data._id}'
	AND taa. STATUS != 'Init'
	GROUP BY
		substring(turl.create_time , 1 , 10)
	ORDER BY
		substring(turl.create_time , 1 , 10) DESC
) d ON a.date = d.date
LEFT JOIN(
	SELECT
		substring(turl.create_time , 1 , 10) date ,
		count(DISTINCT(turl.user_id)) NO
	FROM
		t_user_registry_log_qyl turl
	INNER JOIN t_customer_risk_log tcrl ON tcrl.user_id = turl.user_id
	WHERE
		type = 'Identify'
	AND turl.channel = '${data._id}'
	AND tcrl.prod_code = 'QYL'
	AND risk_status = 'success'
	GROUP BY
		substring(turl.create_time , 1 , 10)
	ORDER BY
		substring(turl.create_time , 1 , 10) DESC
) e ON e.date = a.date
LEFT JOIN(
	SELECT
		substring(turl.create_time , 1 , 10) date ,
		count(DISTINCT(turl.user_id)) p ,
		count(loan_order_id) NO ,
		sum(llo.ought_amount) amount
	FROM
		t_user_registry_log_qyl turl
	INNER JOIN t_user_customer_qyl tucq ON tucq.user_id = turl.user_id
	INNER JOIN lo_loan_order llo ON llo.user_ref = tucq.customer_id
	WHERE
		llo.business_code = 'QYL'
	AND llo.loan_status IN('01')
	AND turl.channel = '${data._id}' GROUP BY substring(turl.create_time , 1 , 10) ORDER BY substring(turl.create_time , 1 , 10) DESC
) f ON a.date = f.date
    `;
  }
  ykqjDataFunnel(data) {
    return `
             #数据漏斗按照用户注册日期的维度
SELECT 
    a.dates AS 日期,
    '一刻千金' as 活动归属,
    a.num AS 注册人数,
    b.num AS 完善资料人数,
    CONCAT(LEFT(b.num / a.num * 100, 4), '%') AS 完善资料率,
    i.num AS 通过风控数,
    CONCAT(LEFT(i.num / a.num * 100, 4), '%') AS 风控通过率,
    c.num AS 建立账户数,
    CONCAT(LEFT(c.num / a.num * 100, 4), '%') AS 建立账户率,
    d.num AS 获得额度数,
    CONCAT(LEFT(d.num / a.num * 100, 4), '%') AS 获得额度率,
    e.num AS 下单数,
    CONCAT(LEFT(e.num / a.num * 100, 4), '%') AS 下单率,
    e.amount as 下单金额,
    f.amount as 发货金额

FROM
    (SELECT 
        DATE_FORMAT(turl.create_time, '%Y-%m-%d') dates,
            COUNT(turl.user_id) num
    FROM
        base.t_user_registry_log turl
    WHERE
        turl.prod_code = 'YKQJ'
        and turl.channel='${data._id}'
    GROUP BY dates
    ORDER BY dates) a
        LEFT JOIN
    (SELECT 
        DATE_FORMAT(turl.create_time, '%Y-%m-%d') dates,
            COUNT(DISTINCT (turl.user_id)) num
    FROM
        base.t_user_registry_log turl
    INNER JOIN base.t_account_application taa ON turl.user_id = taa.user_id
    WHERE
        taa.prod_code = 'YKQJ'
            AND taa.status != 'Init'
            AND turl.prod_code = 'YKQJ'
            and turl.channel='${data._id}'
    GROUP BY dates
    ORDER BY dates) b ON a.dates = b.dates
        LEFT JOIN
    (SELECT 
        DATE_FORMAT(turl.create_time, '%Y-%m-%d') dates,
            COUNT(DISTINCT (turl.user_id)) num
    FROM
        base.t_user_registry_log turl
    LEFT JOIN base.t_customer_risk_log tcrl ON turl.user_id = tcrl.user_id
    WHERE
        turl.prod_code = 'YKQJ'
            AND tcrl.prod_code = 'YKQJ'
            and turl.channel='${data._id}'
            AND tcrl.four_key_status = 'Success'
            AND tcrl.risk_status = 'Success'
    GROUP BY dates
    ORDER BY dates) i ON a.dates = i.dates
        LEFT JOIN
    (SELECT 
        DATE_FORMAT(turl.create_time, '%Y-%m-%d') dates,
            COUNT(DISTINCT (turl.user_id)) num
    FROM
        base.t_user_registry_log turl
    LEFT JOIN base.t_account_application taa ON turl.user_id = taa.user_id
    WHERE
        turl.prod_code = 'YKQJ'
        and turl.channel='${data._id}'
            AND taa.prod_code = 'YKQJ'
            AND taa.status = 'Success'
    GROUP BY dates
    ORDER BY dates) c ON a.dates = c.dates
        LEFT JOIN
    (SELECT 
        DATE_FORMAT(turl.create_time, '%Y-%m-%d') dates,
            COUNT(DISTINCT (turl.user_id)) num
    FROM
        base.t_user_registry_log turl
    LEFT JOIN base.t_user_customer_ykqj tucy ON tucy.user_id = turl.user_id
    LEFT JOIN base.t_customer_sub_account tcsa ON tcsa.user_id = tucy.customer_id
    WHERE
        turl.prod_code = 'YKQJ'
        and turl.channel='${data._id}'
            AND tcsa.product_code = 'YKQJ'
            AND tcsa.total_credit > 0
            #AND tcsa.status = 1
    GROUP BY dates
    ORDER BY dates) d ON d.dates = a.dates
    LEFT JOIN
    (SELECT 
        COUNT(DISTINCT (turl.user_id)) num,
        sum(llo.loan_amount) amount,
            DATE_FORMAT(turl.create_time, '%Y-%m-%d') dates
    FROM
        base.t_user_registry_log turl
    inner JOIN base.t_user_customer_ykqj tucy ON tucy.user_id = turl.user_id
    inner join base.lo_loan_order llo on tucy.customer_id = llo.user_ref
    where turl.prod_code = 'YKQJ' and tucy.prod_code = 'YKQJ'
    and turl.channel='${data._id}'
    and   llo.business_code = 'YKQJ' 
    GROUP BY dates
    ORDER BY dates) e ON e.dates = a.dates
     LEFT JOIN
    (SELECT 
        COUNT(DISTINCT (turl.user_id)) num,
        sum(llo.loan_amount) amount,
            DATE_FORMAT(turl.create_time, '%Y-%m-%d') dates
    FROM
        base.t_user_registry_log turl
    inner JOIN base.t_user_customer_ykqj tucy ON tucy.user_id = turl.user_id
    inner join base.lo_loan_order llo on tucy.customer_id = llo.user_ref
    where turl.prod_code = 'YKQJ' and tucy.prod_code = 'YKQJ'
    and turl.channel='${data._id}'
    and   llo.business_code = 'YKQJ' and llo.loan_status = '01' and llo.returnable_loan_amount > 0 
    GROUP BY dates
    ORDER BY dates) f ON f.dates = a.dates

    
    `;
  }
}

module.exports = new Sqls();
